﻿if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_Invoices]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_Invoices]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.sp_Invoices
	(
	@OrderID int = Null
	)
AS
SET NOCOUNT ON
SELECT      [OrderDetail].OrderId as [ID], -- [Order].ID, 
            [Order].CustomerId, [Order].ShipName, [Order].ShipAddress, 
            [Order].ShipCity, [Order].ShipRegion, [Order].ShipPostalCode, 
            [Order].ShipCountry, [Order].OrderDate, [Order].RequiredDate, 
            [Order].ShippedDate, OrderDetail.UnitPrice, 
            OrderDetail.Quantity, OrderDetail.Discount, 
            CONVERT(money, (OrderDetail.UnitPrice * OrderDetail.Quantity) * (1 - OrderDetail.Discount) / 100 * 100) AS ExtendedPrice, 
            [Order].Freight, OrderDetail.ProductID, [Order].ShipperId, 
            [Order].EmployeeID, 
            OrderDetail.OrderID, 
            Employee.FirstName + N' ' + Employee.LastName AS SalesPerson, 
            Customer.ContactName, Customer.Address, 
            Customer.City, Customer.Region, Customer.PostalCode, 
            Customer.Country, Product.Name AS ProductName, 
            Shipper.Name AS ShipVia
FROM        [Order] INNER JOIN
                  OrderDetail ON [Order].ID = OrderDetail.OrderID INNER JOIN
                  Customer ON [Order].CustomerId = Customer.ID INNER JOIN
                  Employee ON [Order].EmployeeID = Employee.ID INNER JOIN
                  Product ON OrderDetail.ProductID = Product.ID INNER JOIN
                  Shipper ON [Order].ShipperId = Shipper.ID
WHERE     (@OrderID IS NULL) OR
                  (@OrderID = [Order].ID)

GO